﻿using System;
using System.Collections.Generic;
using System.Collections;
using System.Linq;
using System.Web;
using FindIt.BLL;
using MySql.Data.MySqlClient;
using System.Configuration;

namespace FindIt.DAL
{
    public class ImageDB
    {
        private string maxNumImageKeywords = ConfigurationManager.AppSettings["maxNumImageKeywords"];
        private MySqlConnection conn;
        private MySqlCommand cmd;

        public ImageDB()
        {
            conn = new MySqlConnection(ConfigurationManager.AppSettings["connectionString"]);
            cmd = new MySqlCommand();
            cmd.Connection = conn;
        }

        public void AddImage(Image image)
        {
            cmd.CommandText = "INSERT INTO tblImage (Title, URL, Size, IsRestricted, UploadDate, LastUpdate, NumViews, LastViewed, Status, Owner) "
                                        + "VALUES(@title, @url, @size, @isRestricted, @uploadDate, @lastUpdate, @numViews, @lastViewed, @status, @owner)";

            cmd.Parameters.Add("@title", MySqlDbType.VarChar).Value = image.Title;
            cmd.Parameters.Add("@url", MySqlDbType.VarChar).Value = image.URL;
            cmd.Parameters.Add("@size", MySqlDbType.Int32).Value = image.Size;
            cmd.Parameters.Add("@isRestricted", MySqlDbType.Bit).Value = image.IsRestricted;
            cmd.Parameters.Add("@uploadDate", MySqlDbType.DateTime).Value = image.UploadDate;
            cmd.Parameters.Add("@lastUpdate", MySqlDbType.DateTime).Value = image.LastUpdate;
            cmd.Parameters.Add("@numViews", MySqlDbType.Int32).Value = image.NumViews;
            cmd.Parameters.Add("@lastViewed", MySqlDbType.DateTime).Value = image.LastViewed;
            cmd.Parameters.Add("@status", MySqlDbType.Enum).Value = image.Status;
            cmd.Parameters.Add("@owner", MySqlDbType.VarChar).Value = image.Owner;

            try
            {
                conn.Open();
                cmd.ExecuteNonQuery();
                cmd.Parameters.Clear();
            }

            catch (MySqlException ex)
            {
                string msg = "Insert Error:";
                msg += ex.Message;
                throw new Exception(msg);
            }

            finally
            {
                conn.Close();
            }
        }

        public void AddKeyword(string url, string key)
        {
            cmd.CommandText = "INSERT INTO tblImageKeyword (Keyword, ImageURL) "
                                        + "VALUES(@key, @url)";

            cmd.Parameters.Add("@url", MySqlDbType.VarChar).Value = url;
            cmd.Parameters.Add("@key", MySqlDbType.VarChar).Value = key;

            try
            {
                conn.Open();
                cmd.ExecuteNonQuery();
            }

            catch (MySqlException ex)
            {
                string msg = "Insert Error:";
                msg += ex.Message;
                throw new Exception(msg);
            }

            finally
            {
                conn.Close();
            }
        }

        public void AddKeywords(string url, HashSet<string> keys)
        {
            cmd.CommandText = "INSERT INTO tblImageKeyword (Keyword, ImageURL) "
                                        + "VALUES(@key, @url)";
            try
            {
                foreach (string key in keys)
                {
                    cmd.Parameters.Add("@url", MySqlDbType.VarChar).Value = url;
                    cmd.Parameters.Add("@key", MySqlDbType.VarChar).Value = key;

                    conn.Open();
                    cmd.ExecuteNonQuery();
                    cmd.Parameters.Clear();
                    conn.Close();
                }
            }

            catch (MySqlException ex)
            {
                string msg = "Insert Error:";
                msg += ex.Message;
                throw new Exception(msg);
            }
        }

        public void AddTentativeKeyword(string url, string key, string voter)
        {
            cmd.CommandText = "INSERT INTO tblTentativeImageKeyword (Keyword, ImageURL, Voter) "
                                                           + "VALUES(@key, @url, @voter)";

            cmd.Parameters.Add("@url", MySqlDbType.VarChar).Value = url;
            cmd.Parameters.Add("@key", MySqlDbType.VarChar).Value = key;
            cmd.Parameters.Add("@voter", MySqlDbType.VarChar).Value = voter;

            try
            {
                conn.Open();
                cmd.ExecuteNonQuery();
            }

            catch (MySqlException ex)
            {
                string msg = "Insert Error:";
                msg += ex.Message;
                throw new Exception(msg);
            }

            finally
            {
                conn.Close();
            }
        }

        public MySqlDataReader GetImageByURL(string url)
        {
            cmd.CommandText = "SELECT * FROM tblImage WHERE URL = @url";

            cmd.Parameters.Add("@url", MySqlDbType.VarChar).Value = url;

            MySqlDataReader dr;

            try
            {
                conn.Open();
                dr = cmd.ExecuteReader(System.Data.CommandBehavior.CloseConnection);
            }

            catch (MySqlException ex)
            {
                string msg = "Query Error:";
                msg += ex.Message;
                throw new Exception(msg);
            }

            return dr;
        }

        public string GetUnknownCaptchaImage()
        {
            cmd.CommandText = "SELECT ImageURL FROM tblTentativeImageKeyword ORDER BY RAND() LIMIT 1";

            string imageURL;

            try
            {
                conn.Open();
                imageURL = cmd.ExecuteScalar().ToString();
            }

            catch (MySqlException ex)
            {
                string msg = "Query Error:";
                msg += ex.Message;
                throw new Exception(msg);
            }

            finally
            {
                conn.Close();
            }

            return imageURL;
        }

        public string GetKnownCaptchaImage()
        {
            cmd.CommandText = "SELECT ImageURL FROM tblImageKeyword GROUP BY ImageURL HAVING COUNT(ImageURL) = @maxNumImageKeywords ORDER BY RAND() LIMIT 1";

            cmd.Parameters.AddWithValue("@maxNumImagekeywords", maxNumImageKeywords);
            string imageURL;

            try
            {
                conn.Open();
                imageURL = cmd.ExecuteScalar().ToString();
            }

            catch (MySqlException ex)
            {
                string msg = "Query Error:";
                msg += ex.Message;
                throw new Exception(msg);
            }

            finally
            {
                conn.Close();
            }

            return imageURL;
        }

        public bool CheckIfPermanentKeyword(string url, string key)
        {
            cmd.CommandText = "SELECT IF(EXISTS (SELECT Keyword FROM tblImageKeyword WHERE ImageURL = @url && Keyword = @key), true ,false) FROM DUAL";

            cmd.Parameters.Add("@url", MySqlDbType.VarChar).Value = url;
            cmd.Parameters.Add("@key", MySqlDbType.VarChar).Value = key;

            bool result;

            try
            {
                conn.Open();
                string resultString = cmd.ExecuteScalar().ToString();
                if (int.Parse(resultString) == 1)
                    result = true;
                else
                    result = false;
            }

            catch (MySqlException ex)
            {
                string msg = "Query Error:";
                msg += ex.Message;
                throw new Exception(msg);
            }

            finally
            {
                conn.Close();
            }

            return result;
        }

        public int GetNumberOfVotesForTentativeKeyword(string url, string key)
        {
            cmd.CommandText = "SELECT COUNT(*) FROM tblTentativeImageKeyword WHERE ImageURL = @url && Keyword = @key;";

            cmd.Parameters.Add("@url", MySqlDbType.VarChar).Value = url;
            cmd.Parameters.Add("@key", MySqlDbType.VarChar).Value = key;

            int count;

            try
            {
                conn.Open();
                count = int.Parse(cmd.ExecuteScalar().ToString());
            }

            catch (MySqlException ex)
            {
                string msg = "Query Error:";
                msg += ex.Message;
                throw new Exception(msg);
            }

            finally
            {
                conn.Close();
            }

            return count;
        }

        public bool CheckIfVoterForTentativeKeyword(string url, string key, string voter)
        {
            cmd.CommandText = "SELECT IF(EXISTS (SELECT Voter FROM tblTentativeImageKeyword WHERE ImageURL = @url && Keyword = @key && Voter = @voter), 1 ,0) FROM DUAL";

            cmd.Parameters.Add("@url", MySqlDbType.VarChar).Value = url;
            cmd.Parameters.Add("@key", MySqlDbType.VarChar).Value = key;
            cmd.Parameters.Add("@voter", MySqlDbType.VarChar).Value = voter;

            bool result;

            try
            {
                conn.Open();
                string resultString = cmd.ExecuteScalar().ToString();
                if (int.Parse(resultString) == 1)
                    result = true;
                else
                    result = false;
            }

            catch (MySqlException ex)
            {
                string msg = "Query Error:";
                msg += ex.Message;
                throw new Exception(msg);
            }

            finally
            {
                conn.Close();
            }

            return result;
        }

        public MySqlDataReader GetVoters(string url, string key)
        {
            cmd.CommandText = "SELECT voter from tblTentativeImageKeyword WHERE ImageURL = @url && Keyword = @key";

            cmd.Parameters.Add("@url", MySqlDbType.VarChar).Value = url;
            cmd.Parameters.Add("@key", MySqlDbType.VarChar).Value = key;

            MySqlDataReader dr;

            try
            {
                conn.Open();
                dr = cmd.ExecuteReader(System.Data.CommandBehavior.CloseConnection);
            }

            catch (MySqlException ex)
            {
                string msg = "Query Error:";
                msg += ex.Message;
                throw new Exception(msg);
            }

            return dr;
        }

        public void RemoveTentativeKeyword(string url, string key)
        {
            cmd.CommandText = "DELETE FROM tblTentativeImageKeyword WHERE ImageURL = @url AND Keyword = @key";

            cmd.Parameters.Add("@url", MySqlDbType.VarChar).Value = url;
            cmd.Parameters.Add("@key", MySqlDbType.VarChar).Value = key;

            try
            {
                conn.Open();
                cmd.ExecuteNonQuery();
            }

            catch (MySqlException ex)
            {
                string msg = "Query Error:";
                msg += ex.Message;
                throw new Exception(msg);
            }

            finally
            {
                conn.Close();
            }
        }

        public void RemoveAllTentativeKeywords(string url)
        {
            cmd.CommandText = "DELETE FROM tblTentativeImageKeyword WHERE ImageURL = @url";

            cmd.Parameters.Add("@url", MySqlDbType.VarChar).Value = url;

            try
            {
                conn.Open();
                cmd.ExecuteNonQuery();
            }

            catch (MySqlException ex)
            {
                string msg = "Query Error:";
                msg += ex.Message;
                throw new Exception(msg);
            }

            finally
            {
                conn.Close();
            }
        }

        public int GetNumberOfPermanentKeywords(string url)
        {
            cmd.CommandText = "SELECT COUNT(*) FROM tblImageKeyword WHERE ImageURL = @url";

            cmd.Parameters.Add("@url", MySqlDbType.VarChar).Value = url;

            int count;

            try
            {
                conn.Open();
                count = int.Parse(cmd.ExecuteScalar().ToString());
            }

            catch (MySqlException ex)
            {
                string msg = "Query Error:";
                msg += ex.Message;
                throw new Exception(msg);
            }

            finally
            {
                conn.Close();
            }

            return count;
        }
    }
}